from sqlalchemy import create_engine, Column, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime
from pathlib import Path

# 创建数据库目录
DB_DIR = Path("db")
DB_DIR.mkdir(exist_ok=True)

# 创建数据库引擎
SQLALCHEMY_DATABASE_URL = f"sqlite:///{DB_DIR}/voice_data.db"
engine = create_engine(SQLALCHEMY_DATABASE_URL)

# 创建会话工厂
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 创建基类
Base = declarative_base()

class VoiceData(Base):
    """语音数据模型"""
    __tablename__ = "voice_data"

    id = Column(Integer, primary_key=True, index=True)
    voice_path = Column(String, index=True)  # 音频文件路径
    text = Column(String)                    # 文本内容
    language = Column(String, index=True)    # 语言
    created_at = Column(DateTime, default=datetime.utcnow)  # 创建时间

# 创建数据库表
Base.metadata.create_all(bind=engine)


def get_db():
    """获取数据库会话"""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


def add_voice_data(db, voice_path: str, text: str, language: str):
    """添加语音数据"""
    db_voice = VoiceData(voice_path=voice_path, text=text, language=language)
    db.add(db_voice)
    db.commit()
    db.refresh(db_voice)
    return db_voice


def get_voice_data_by_language(db, language: str, page: int = 1, page_size: int = 20):
    """获取指定语言的语音数据，支持分页

    Args:
        db: 数据库会话
        language: 语言代码
        page: 页码, 从1开始
        page_size: 每页数据量

    Returns:
        items: 当前页的数据列表
        total: 总数据量
        total_pages: 总页数
    """
    # 计算总数
    total = db.query(VoiceData).filter(VoiceData.language == language).count()

    # 计算总页数
    total_pages = (total + page_size - 1) // page_size

    # 计算偏移量
    offset = (page - 1) * page_size

    # print(f"Total: {total}, Total pages: {total_pages}, Offset: {offset}")

    # 获取当前页的数据
    items = db.query(VoiceData).filter(
        VoiceData.language == language
    ).order_by(
        VoiceData.created_at
    ).offset(
        offset
    ).limit(
        page_size
    ).all()

    # for item in items:
    #     print(f"Item: {item.id}, voice_path={item.voice_path}, text={item.text}, language={item.language}, created_at={item.created_at}")

    return {
        "items": items,
        "total": total,
        "total_pages": total_pages
    }


def get_voice_data_count(db, language: str = None):
    """获取语音数据数量"""
    query = db.query(VoiceData)
    if language:
        query = query.filter(VoiceData.language == language)
    return query.count()


def get_voice_data_by_id(db, id: int):
    """通过ID获取语音数据

    Args:
        db: 数据库会话
        id: 数据ID

    Returns:
        VoiceData: 语音数据对象，如果不存在返回None
    """
    return db.query(VoiceData).filter(VoiceData.id == id).first()


def check_voice_data_exists(db, text: str, language: str) -> bool:
    """检查语音数据是否已存在

    Args:
        db: 数据库会话
        text: 文本内容
        language: 语言代码

    Returns:
        bool: 如果数据已存在返回 True, 否则返回 False
    """
    return db.query(VoiceData).filter(
        VoiceData.text == text,
        VoiceData.language == language
    ).first() is not None
